﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Diagnostics;
using System.IO;
using System.Web;
using System.Threading;
using System.Data;
using System.Data.OracleClient;
using System.Configuration;

namespace DQYT.HTDZ.BusinessService
{

    /// <summary>
    /// 20060207
    /// Add By Joker
    /// 数据操作公共层
    /// 提供一些存取数据库的接口函数，保持一个数据库链接。
    /// </summary>
    public sealed class DataService
    {
        /// <summary>
        /// 数据库连接
        /// </summary>
        private OracleConnection oraConnection;

        /// <summary>
        /// ora的连接方式
        /// </summary>
        public string szOraConn;
        /// <summary>
        /// ole的连接方式
        /// </summary>
        public string szOleConn;

        /// <summary>
        /// 构造器
        /// </summary>
        public DataService()
        {

            this.szOraConn = ConfigurationSettings.AppSettings["OraConString"];
            this.oraConnection = new OracleConnection(this.szOraConn);
            this.szOleConn = ConfigurationSettings.AppSettings["OleConString"];

        }

        #region 利用默认的连接串OraConString 和 数据库组件oracleclient操作数据库
        /// <summary>
        /// 返回当前数据库连接
        /// </summary>
        /// <returns></returns>

        public OracleConnection GetOraConn()
        {
            if (this.oraConnection.State == ConnectionState.Closed)
            {
                this.oraConnection.Open();
                return this.oraConnection;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 执行SQL查询，返回结果的第一行第一列
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <returns>查询结果</returns>
        public object GetValue(string sql)
        {
            OracleCommand OraCmd = new OracleCommand(sql, this.oraConnection);
            OraCmd.CommandType = CommandType.Text;

            try
            {
                this.oraConnection.Open();
                object o = OraCmd.ExecuteScalar();
                this.oraConnection.Close();
                return o;
            }
            catch (Exception er)
            {
                //LogErrInfo.WriteErrInfo("数据访问层.txt",er.ToString());	
                throw er;
            }
            finally
            {
                this.oraConnection.Close();
            }
        }

        /// <summary>
        /// 执行SQL语句 返回一个数据集
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <returns></returns>					
        public OracleDataReader GetDataReader(string sql)
        {
            OracleCommand OraCmd = new OracleCommand(sql, this.oraConnection);
            OraCmd.CommandType = CommandType.Text;

            try
            {
                this.oraConnection.Open();
                return OraCmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception er)
            {
                throw er;
            }

        }

        /// <summary>
        /// 执行SQL语句 返回一个数据集
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <returns></returns>	
        public DataSet GetDataset(string sql)
        {
            OracleDataAdapter OraAdapter = new OracleDataAdapter(sql, this.oraConnection);
            DataSet myDataSet = new DataSet();

            try
            {
                this.oraConnection.Open();
                OraAdapter.Fill(myDataSet);
                return myDataSet;
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                this.oraConnection.Close();
            }
        }

        /// <summary>
        /// 执行SQL语句 返回一个数据表
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <returns></returns>	
        public DataTable GetTable(string sql)
        {
            OracleDataAdapter OraAdapter = new OracleDataAdapter(sql, this.oraConnection);
            DataSet myDataSet = new DataSet();

            try
            {
                this.oraConnection.Open();
                OraAdapter.Fill(myDataSet);
                return myDataSet.Tables[0];
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                this.oraConnection.Close();
            }
        }

        /// <summary>
        /// 执行SQL语句 返回一个数据表，代分页效果，只显示当前页数的记录数
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <param name="nPage">当前页指针</param>
        /// <param name="nPageSize">页面记录行数量</param>
        /// <param name="nRecsCount">总记录数</param>
        /// <param name="nEndRecs">当前页终止记录行</param>
        /// <param name="nStartRecs">当前页起始记录行行</param>
        /// <returns></returns>	
        public DataTable GetTable(string sql, int nPage, int nPageSize, out int nRecsCount, out int nStartRecs, out int nEndRecs)
        {
            OracleDataAdapter OraAdapter = new OracleDataAdapter(sql, this.oraConnection);
            DataSet myDataSet = new DataSet();

            try
            {
                //得到当前sql语句总记录行
                string strSqlCount = "Select Count(*) From ( " + sql + ")";
                object o = this.GetValue(strSqlCount);
                if (o != null)
                {
                    nRecsCount = Convert.ToInt32(o);
                }
                else
                {
                    nRecsCount = 0;
                }
                //当前页的起始行		
                nStartRecs = (nPage - 1) * nPageSize + 1;
                //当前页的终止行，如果计算操作范围则以当前表最大记录数为主
                nEndRecs = nPage * nPageSize;
                if (nEndRecs > nRecsCount)
                {
                    nEndRecs = nRecsCount;
                }

                this.oraConnection.Open();
                OraAdapter.Fill(myDataSet, nStartRecs - 1, nPageSize, "Table1");
                return myDataSet.Tables["Table1"];

            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                this.oraConnection.Close();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="nPage"></param>
        /// <param name="nPageSize"></param>
        /// <param name="nRecsCount"></param>
        /// <returns></returns>
        public DataTable GetTable(string sql, int nPage, int nPageSize, out int nRecsCount, out int nPageCount)
        {
            OracleDataAdapter OraAdapter = new OracleDataAdapter(sql, this.oraConnection);
            DataSet myDataSet = new DataSet();

            try
            {
                //得到当前sql语句总记录行
                string strSqlCount = "Select Count(*) From ( " + sql + " )";
                object o = this.GetValue(strSqlCount);
                if (o != null)
                {
                    nRecsCount = Convert.ToInt32(o);
                }
                else
                {
                    nRecsCount = 0;
                }
                //当前页的起始行		
                int nStartRecs = (nPage - 1) * nPageSize + 1;
                //当前页的终止行，如果计算操作范围则以当前表最大记录数为主
                int nEndRecs = nPage * nPageSize;
                if (nEndRecs > nRecsCount)
                {
                    nEndRecs = nRecsCount;
                }
                //总的页数记录
                if ((nRecsCount % nPageSize) == 0)
                {
                    nPageCount = nRecsCount / nPageSize;
                }
                else
                {
                    nPageCount = nRecsCount / nPageSize + 1;
                }

                this.oraConnection.Open();
                OraAdapter.Fill(myDataSet, nStartRecs - 1, nPageSize, "Table1");
                return myDataSet.Tables["Table1"];

            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                this.oraConnection.Close();
            }
        }


        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="strSql">需要执行的sql语句</param>
        /// <param name="strCon">ora连接字符串</param>
        /// <returns>成功即返回真，否则就返回假</returns>
        public bool ExecOraSql(string strSql)
        {
            bool bSucceeded = false;

            OracleConnection myConnection = new OracleConnection(this.szOraConn);
            myConnection.Open();

            OracleCommand myCommand = myConnection.CreateCommand();
            //			OracleTransaction myTrans;
            //
            //			myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted);
            //			myCommand.Transaction = myTrans;

            try
            {
                myCommand.CommandText = strSql;
                myCommand.ExecuteNonQuery();
                //				myTrans.Commit();
                bSucceeded = true;
            }
            catch (Exception er)
            {
                //				myTrans.Rollback();
                Debug.WriteLine(er.ToString());
            }
            finally
            {
                myConnection.Close();
            }
            return bSucceeded;
        }


        #region 数据适配器 20060620 Add By Joker
        /// <summary>
        /// 根据传入的Sql语句得到数据adapter
        /// </summary>
        /// <param name="sql">select sql </param>
        /// <returns></returns>
        public System.Data.OracleClient.OracleDataAdapter GetAdapter(string strSql)
        {

            OracleCommand cmd = this.oraConnection.CreateCommand();

            cmd.CommandText = strSql;

            cmd.CommandType = CommandType.Text;

            OracleDataAdapter OraAdapter = new OracleDataAdapter(cmd);

            OracleCommandBuilder cb = new OracleCommandBuilder(OraAdapter);

            OraAdapter.DeleteCommand = cb.GetDeleteCommand();
            OraAdapter.UpdateCommand = cb.GetUpdateCommand();
            OraAdapter.InsertCommand = cb.GetInsertCommand();

            return OraAdapter;
        }

        #endregion

        /// <summary>
        /// 下载数据
        /// </summary>
        /// <param name="szUserCode"></param>
        /// <param name="szFeedbackDate"></param>
        /// <param name="szField"></param>
        /// <param name="szSql"></param>
        /// <param name="szFilaPath"></param>
        /// <param name="szFilter"></param>
        public bool DownloadData(string szSql, string szFilePath)
        {
            bool bSeced = false;
            try
            {
                FileStream fstr = new FileStream(szFilePath, FileMode.OpenOrCreate, FileAccess.Write);

                //生成文件
                OracleCommand ocmd = new OracleCommand(szSql, this.oraConnection);
                if (this.oraConnection.State == ConnectionState.Closed)
                    this.oraConnection.Open();

                OracleDataReader odr = ocmd.ExecuteReader();
                OracleLob olob = OracleLob.Null;
                byte[] buffer = new byte[1024000];
                odr.Read();
                //获得第一个字段的值
                olob = odr.GetOracleLob(0);

                int countRead = olob.Read(buffer, 0, buffer.Length);
                while (countRead > 0)
                {
                    fstr.Write(buffer, 0, countRead);
                    countRead = olob.Read(buffer, 0, buffer.Length);
                }
                olob.Close();
                odr.Close();
                fstr.Close();

                bSeced = true;
            }
            catch (Exception er)
            {
                Debug.WriteLine(er.ToString());
            }
            finally
            {
                this.oraConnection.Close();
            }
            return bSeced;
        }

        #endregion

        #region 利用默认的连接串oleConString 和 数据库组件oleConn操作数据库
        /// <summary>
        /// 根据给定的连接字符串执行sql语句来返回具有数据的只读向前的Reader
        /// </summary>
        /// <param name="strSql">提取数据的sql语句</param>
        /// <param name="strCon">数据库连接字符串</param>
        /// <returns>返回含有数据的reader</returns>
        private OleDbDataReader getOleDataReader(string strSql)
        {
            OleDbDataReader reader = null;
            try
            {
                OleDbConnection con = new OleDbConnection(this.szOleConn);

                con.Open();

                OleDbCommand cmd = new OleDbCommand(strSql, con);

                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            }
            catch (Exception er)
            {
                Debug.WriteLine(er.ToString());
                //LogErrInfo.WriteErrInfo("数据访问层.txt",er.ToString());;	
                throw er;
            }

            return reader;
        }


        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="strSql">需要执行的sql语句</param>
        /// <param name="strCon">ole连接字符串</param>
        /// <returns>成功就返回真，否则就返回假</returns>
        private bool ExecOleSql(string strSql)
        {
            bool bSucceeded = false;
            OleDbConnection myConnection = new OleDbConnection(this.szOleConn);
            myConnection.Open();

            OleDbCommand myCommand = myConnection.CreateCommand();
            OleDbTransaction myTrans;

            myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted);
            myCommand.Connection = myConnection;
            myCommand.Transaction = myTrans;

            try
            {
                myCommand.CommandText = strSql;
                myCommand.ExecuteNonQuery();
                myTrans.Commit();
                bSucceeded = true;
            }
            catch (Exception e)
            {

                Debug.WriteLine(e.ToString());
                //LogErrInfo.WriteErrInfo("数据访问层.txt",e.ToString());;	

                try
                {
                    myTrans.Rollback();
                }
                catch (OleDbException er)
                {
                    if (myTrans.Connection != null)
                    {
                        Debug.WriteLine(er.ToString());
                        //LogErrInfo.WriteErrInfo("数据访问层.txt",er.ToString());;	
                    }
                }
            }
            finally
            {
                myConnection.Close();
            }
            return bSucceeded;
        }

        /// <summary>
        /// 执行SQL语句 返回一个数据集
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <returns></returns>	
        public DataSet GetOleDataset(string sql)
        {
            OleDbConnection OleConnection = new OleDbConnection(this.szOleConn);
            OleConnection.Open();

            OleDbDataAdapter OleAdapter = new OleDbDataAdapter(sql, this.szOleConn);
            DataSet myDataSet = new DataSet();

            try
            {
                OleConnection.Open();
                OleAdapter.Fill(myDataSet);
                return myDataSet;
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                OleConnection.Close();
            }
        }
        #endregion

        #region 公共的加密接口
        /// <summary>
        /// 密文处理
        /// </summary>
        /// <param name="CodeString">密文</param>
        /// <returns>加密后的字符串</returns>
        public string EncryptCode(string strCode)
        {
            byte[] byCode = ASCIIEncoding.ASCII.GetBytes(strCode);
            byte[] byKey = ASCIIEncoding.ASCII.GetBytes("JrscSoft");
            byte[] byResult = new byte[strCode.Length];

            int j = 0;
            for (int i = 0; i < strCode.Length; i++)
            {
                j = j == byKey.Length ? 1 : j++;
                byResult[i] = (byte)(byCode[i] ^ byKey[j]);
            }

            return ASCIIEncoding.ASCII.GetString(byResult, 0, byResult.Length);
        }
        #endregion

        #region 利用传入的连接串创建数据库连接操作数据库
        /// <summary>
        /// 判断当前连接串是否正确
        /// </summary>
        /// <param name="szOraCon"></param>
        /// <returns></returns>
        public bool CheckOraConnIsRight(string szOraCon)
        {
            bool bRight = false;
            OracleConnection CurOraCon = GetOraConn(szOraCon);
            if (CurOraCon != null)
            {
                try
                {

                    CurOraCon.Open();

                    bRight = true;

                    CurOraCon.Close();
                }
                catch (Exception er)
                {
                    Debug.WriteLine(er.ToString());
                }
            }
            return bRight;
        }

        /// <summary>
        /// 返回当前数据库连接
        /// </summary>
        /// <returns></returns>
        public OracleConnection GetOraConn(string szOraCon)
        {
            if (szOraCon.Length == 0)
                return null;
            OracleConnection CurOraCon = new OracleConnection(szOraCon);
            try
            {
                if (CurOraCon.State == ConnectionState.Closed)
                {
                    return CurOraCon;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception er)
            {
                return null;
            }
        }

        /// <summary>
        /// 执行SQL查询，返回结果的第一行第一列
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <returns>查询结果</returns>
        public object GetValue(string szOraCon, string sql)
        {
            OracleConnection CurOraCon = this.GetOraConn(szOraCon);

            if (CurOraCon == null)
                return null;

            OracleCommand OraCmd = new OracleCommand(sql, CurOraCon);
            OraCmd.CommandType = CommandType.Text;

            try
            {
                CurOraCon.Open();
                object o = OraCmd.ExecuteScalar();
                CurOraCon.Close();
                return o;
            }
            catch (Exception er)
            {
                Debug.WriteLine(er.ToString());
                throw er;
            }
            finally
            {
                CurOraCon.Close();
            }
        }

        /// <summary>
        /// 执行SQL语句 返回一个数据集
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <returns></returns>					
        public OracleDataReader GetDataReader(string szOraCon, string sql)
        {
            OracleConnection CurOraCon = this.GetOraConn(szOraCon);

            if (CurOraCon == null)
                return null;

            OracleCommand OraCmd = new OracleCommand(sql, CurOraCon);
            OraCmd.CommandType = CommandType.Text;

            try
            {
                CurOraCon.Open();
                return OraCmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception er)
            {
                throw er;
            }
        }

        /// <summary>
        /// 执行SQL语句 返回一个数据集
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <returns></returns>	
        public DataSet GetDataset(string szOraCon, string sql)
        {
            OracleConnection CurOraCon = this.GetOraConn(szOraCon);

            if (oraConnection == null)
                return null;

            OracleDataAdapter OraAdapter = new OracleDataAdapter(sql, CurOraCon);
            DataSet myDataSet = new DataSet();

            try
            {
                CurOraCon.Open();
                OraAdapter.Fill(myDataSet);
                return myDataSet;
            }
            catch (Exception er)
            {
                throw er;
            }
            finally
            {
                CurOraCon.Close();
            }
        }

        /// <summary>
        /// 执行SQL语句 返回一个数据集
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <returns></returns>	
        public DataTable GetTable(string szOraCon, string sql)
        {
            OracleConnection CurOraCon = this.GetOraConn(szOraCon);

            if (CurOraCon == null)
                return null;

            OracleDataAdapter OraAdapter = new OracleDataAdapter(sql, CurOraCon);
            DataSet myDataSet = new DataSet();

            try
            {
                CurOraCon.Open();
                OraAdapter.Fill(myDataSet);
                return myDataSet.Tables[0];
            }
            catch (Exception er)
            {
                Debug.WriteLine(er.ToString());
                throw er;
            }
            finally
            {
                CurOraCon.Close();
            }
        }



        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="strSql">需要执行的sql语句</param>
        /// <param name="strCon">ora连接字符串</param>
        /// <returns>成功即返回真，否则就返回假</returns>
        public bool ExecOraSql(string szOraCon, string strSql)
        {
            bool bSucceeded = false;
            OracleConnection myConnection = this.GetOraConn(szOraCon);

            if (myConnection == null)
                return bSucceeded;

            myConnection.Open();

            OracleCommand myCommand = myConnection.CreateCommand();
            OracleTransaction myTrans;

            myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted);
            myCommand.Transaction = myTrans;

            try
            {
                myCommand.CommandText = strSql;
                myCommand.ExecuteNonQuery();
                myTrans.Commit();
                bSucceeded = true;
            }
            catch (Exception er)
            {
                myTrans.Rollback();
                Debug.WriteLine(er.ToString());
            }
            finally
            {
                myConnection.Close();
            }
            return bSucceeded;
        }

        /// <summary>
        /// 执行oracle系统级的sql语句
        /// </summary>
        /// <param name="strSql">需要执行的sql语句</param>
        /// <param name="strCon">ora连接字符串</param>
        /// <returns>成功即返回真，否则就返回假</returns>
        public bool ExecSysOraSql(string szOraCon, string strSql)
        {
            bool bSucceeded = false;
            OracleConnection myConnection = this.GetOraConn(szOraCon);

            if (myConnection == null)
                return bSucceeded;

            myConnection.Open();

            OracleCommand myCommand = myConnection.CreateCommand();
            myCommand.CommandType = CommandType.Text;


            try
            {
                myCommand.CommandText = strSql;
                myCommand.ExecuteNonQuery();
                bSucceeded = true;
            }
            catch (Exception er)
            {
                Debug.WriteLine(er.ToString());
            }
            finally
            {
                myConnection.Close();
            }
            return bSucceeded;
        }

        #endregion

    }
}